#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
============================================================================
    Desc: 每日检查数据库备份情况
    Author: zhoubangjun
=============================================================================
"""
import json
import logging
import os
import time
import datetime
import yaml
import re
import jinja2
import subprocess

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
from email.mime.text import MIMEText
from email.header import Header
from email.utils import formataddr

base_dir = os.path.dirname(os.path.abspath(__file__))
archive_dir = os.path.join(base_dir, 'archive')
config_file = os.path.join(base_dir, 'config.yml')
mail_info_file = os.path.join(base_dir, '.mail_info.json')
log_file = os.path.join(base_dir, 'db_backup_inspection.log')
today = time.strftime("%Y-%m-%d", time.localtime(time.time()))
if not os.path.exists(archive_dir):
    os.mkdir(archive_dir)

with open(mail_info_file) as f:
    mail_info = json.load(f)
smtp_host = mail_info['smtp_host']
username = mail_info['username']
password = mail_info['password']
topic = mail_info['topic'].format(today)
to_addr = mail_info['to_addr']
cc_addr = mail_info['cc_addr']

report_name = topic
report_path = f'{archive_dir}/{report_name}.csv'

logger = logging.getLogger()
logger.setLevel(logging.DEBUG)
handler = logging.FileHandler(filename=log_file, mode='w', encoding='utf-8')
handler.setFormatter(logging.Formatter("%(asctime)s-%(name)s-%(levelname)s: %(message)s"))
logger.addHandler(handler)


def db_backup_inspection():
    """
    backup_info_list = [
        {
        "name": "公共mysql",        
        "ip": "10.240.58.77",
        "db_category": "mysql",
        "backup_date": "2024/1/9",
        "backup_status": "success",
        "backup_content": "列表",
        "remark": ""
        },    
    ]
    backup_failed_info = {
            "mysql": ["10.1.1.1", "10.1.1.2"],
            "cassandra": ["10.1.1.1", "10.1.2.3"]
        }
    """
    db_category_set = set()
    backup_info_list = []
    backup_status = "成功"
    backup_failed_info = {}
    grep_date = datetime.date.today().strftime("%Y%m%d")

    config_info = yaml.load(open(config_file).read(), Loader=yaml.FullLoader)

    for IP, info in config_info.items():
        name = info['name'].strip()
        db_category = info['db_category'].strip()
        backup_config = info['config']
        backup_path = backup_config['backup_path']
        time_format = backup_config['time_format']
        find_type = backup_config.get('find_type', None)
        backup_frequency = backup_config.get('backup_frequency', '')
        remarks = backup_config.get('remarks', '')
        run_cmd = info.get('run_cmd', None)
        backup_date = time.strftime("{}".format(time_format), time.localtime(time.time()))

        # 计算今天是周几（0代表周一，1代表周二，...，6代表周日）
        if backup_frequency == 'saturday':
            today = datetime.date.today()
            backup_date = saturday = today + datetime.timedelta((5 - today.weekday()) % 7)
            if saturday > today:
                backup_date = today - datetime.timedelta((today.weekday() + 2) % 7)
            backup_date = backup_date.strftime(time_format)

        db_category_set.add(db_category)

        grep_backup_log_cmd = f"""grep -P "{grep_date}.*failed" {backup_path}/backup.log || echo 0"""
        if run_cmd:
            logging.info(f'run_cmd: {run_cmd}')
            find_backup_cmd = run_cmd.format(backup_path=backup_path, find_type=find_type, backup_date=backup_date)
        else:
            find_backup_cmd = f"""find {backup_path} -type {find_type} -name "*{backup_date}*" | xargs -r du -sh"""

        ansible_cmd = "ansible {} -m shell -a '{}'".format(IP, '{}')
        ansible_cmd_find_backup = ansible_cmd.format(find_backup_cmd)
        ansible_cmd_grep_backup_log = ansible_cmd.format(grep_backup_log_cmd)


        backup_info = {
            "ip": IP,
            "name": name,
            "db_category": db_category,
            "backup_date": re.sub(r'/|-|_', '', backup_date),
            "backup_status": backup_status,
            "backup_content": [""],
            "remarks": remarks
        }
        # grep backup.log
        logging.info(f'ansible_cmd_grep_backup_log command: {ansible_cmd_grep_backup_log}')
        return_code, result, stderr = run_ansible_command(ansible_cmd_grep_backup_log)
        logging.info(f"ansible_cmd_grep_backup_log result:[return_code: {return_code}, stdout: {result}, stderr: {stderr}]")

        if return_code != 0 or 'backup failed' in result[-1]:
            logging.error(f"服务器{IP}备份失败: {result}")
            backup_info['backup_status'] = "失败"
            backup_info_list.append(backup_info)
            # backup_failed_info[db_category].append(IP) if backup_failed_info.get(
            #     db_category) else backup_failed_info.__setitem__(db_category, [IP])
            continue

        # find backup
        logging.info(f'ansible_cmd_find_backup command: {ansible_cmd_find_backup}')
        return_code, result, stderr = run_ansible_command(ansible_cmd_find_backup)
        logging.info(f"ansible_cmd_find_backup result:[return_code: {return_code}, stdout: {result}, stderr: {stderr}]")

        if return_code != 0 or not result[1:]:
            logging.error(f"服务器{IP}备份文件查询失败: {result}")
            backup_info['backup_status'] = "失败"
            backup_info_list.append(backup_info)
            # backup_failed_info[db_category].append(IP) if backup_failed_info.get(
            #     db_category) else backup_failed_info.__setitem__(db_category, [IP])
            continue

        backup_info["backup_content"] = [i.replace('\t', '    ').strip() for i in result[1:]]
        backup_info_list.append(backup_info)
        logging.info(f'backup_info: {backup_info}')
        logging.info(f"backup_info_list: {backup_info_list}")

    backup_summary_info = {
        "ip_counts": len(config_info),
        "db_category": db_category_set,
        "backup_failed_info": backup_failed_info
    }
    logging.info(f"backup_summary_info: {backup_summary_info}")

    return backup_summary_info, backup_info_list


def run_ansible_command(command):
    process = subprocess.Popen(command, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)
    stdout, stderr = process.communicate()

    # 将输出从字节转换为字符串
    stdout = stdout.decode('utf-8').strip().split('\n')
    stderr = stderr.decode('utf-8')
    if process.returncode != 0:
        logging.error(f"执行命令[{command}]失败，返回码：{process.returncode}，错误信息：{stderr}")

    return process.returncode, stdout, stderr


def generate_csv(data):
    # encoding='utf-8-sig' 解决office下打开csv文件中文乱码问题
    with open(report_path, 'w', encoding='utf-8-sig') as f:
        f.write('IP,服务器名称,数据库类型,备份日期,备份状态,备份文件路径及备份大小,备注\n')
        for info in data:
            ip = info['ip']
            name = info['name']
            db_category = info['db_category']
            backup_date = info['backup_date']
            backup_status = info['backup_status']
            backup_content = info['backup_content']
            remarks = info['remarks']
            for content in backup_content:
                f.write(f"{ip},{name},{db_category},{backup_date},{backup_status},{content},{remarks}\n")

    logging.info("表格数据生成完毕")


def gen_email_content(data):
    env = jinja2.Environment(loader=jinja2.FileSystemLoader(base_dir))
    temp = env.get_template('email_template.html')
    email_content = temp.render(report_name=report_name, backup_info_list=data)

    return email_content


def send_mail(attachment_path, message, subtype='plain'):
    msg = MIMEMultipart('mixed')
    msg['Subject'] = Header(topic)
    msg['From'] = formataddr(["paas", username])
    msg['To'] = ",".join(to_addr)
    if cc_addr:
        msg['Cc'] = ",".join(cc_addr)

    alternative = MIMEMultipart('alternative')
    textplain = MIMEText(message, _subtype=subtype, _charset='UTF-8')
    alternative.attach(textplain)

    msg.attach(alternative)

    xlsxpart = MIMEApplication(open(attachment_path, 'rb').read())
    xlsxpart.add_header('Content-Disposition', 'attachment', filename=Header(attachment_path.split('/')[-1], "utf-8").encode())
    msg.attach(xlsxpart)
    try:
        client = smtplib.SMTP(smtp_host, 25)
        client.starttls()
        client.login(username, password)
        client.sendmail(username, to_addr, msg.as_string())
        client.quit()
        logging.info('邮件发送成功！')
    except Exception as e:
        logging.error(f'邮件发送失败: {e}')


if __name__ == '__main__':
    backup_summary_info, backup_info_list = db_backup_inspection()
    generate_csv(backup_info_list)
    gen_email_content(data=backup_info_list)
    # message = ""
    # ip_counts = backup_summary_info['ip_counts']
    # db_category = backup_summary_info['db_category']
    # backup_failed_info = backup_summary_info.get('backup_failed_info')
    # db_category_str = '、'.join(list(db_category))
    #
    # if backup_failed_info:
    #     for db_categroy, failed_info in backup_failed_info.items():
    #         message = message + f'{db_categroy}: {failed_info};'
    #
    #     message = f"共巡检 {len(db_category)} 类数据备份，分别为{db_category_str}，涉及服务器 {ip_counts} 台。\n异常备份信息: {message}\n详情见附件。"
    # else:
    #     message = f"共巡检 {len(db_category)} 类数据备份，分别为{db_category_str}，涉及服务器 {ip_counts} 台。\n备份正常，详情见附件。"

    if backup_info_list:
        # send_mail(attachment_path=attachment_path, message=gen_email_content(data=backup_info_list), subtype='html')
        pass